﻿using Nuclear.Models.DbModels;
using Nuclear.Utils;
using SqlSugar;

namespace Nuclear.Services;

public class DbService(IConfiguration configuration) : IDbService
{
    private const string DbName = "nuked";
    private static bool IsProduction => Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") == "Production";
    private static string DbFileName => IsProduction ? $"{DbName}prod.db" : $"{DbName}.db";
    private static readonly string DbFilePath = Path.Combine(Environment.CurrentDirectory, DbFileName);
    private readonly SqlSugarClient _localSqliteDb = new SqlSugarClient(new ConnectionConfig()
    {
        ConnectionString = $"Data Source={DbFilePath}", // SQLite 连接字符串
        DbType = DbType.Sqlite, // 指定数据库类型为 SQLite
        IsAutoCloseConnection = true, // 自动关闭连接
        InitKeyType = InitKeyType.Attribute//从实体特性中读取主键自增列信息
    });
    private readonly SqlSugarClient _crmebMysqlDb = new SqlSugarClient(new ConnectionConfig()
    {
        ConnectionString = configuration.GetValue<string>("CrmebDbConnectionString"),
        DbType = DbType.MySqlConnector, // 指定数据库类型为 SQLite
        IsAutoCloseConnection = true, // 自动关闭连接
        InitKeyType = InitKeyType.Attribute//从实体特性中读取主键自增列信息
    });

    public void CreateDatabaseAndTables()
    {
        if (File.Exists(DbFilePath))
            return;
        _localSqliteDb.DbMaintenance.CreateDatabase(DbName, null);
        _localSqliteDb.CodeFirst.InitTables<SoftwareSystem, ConsumeCode>();
    }

    public async Task<List<ConsumeCode>> GetConsumeCodeAsync(
        string systemId, 
        string? merchantId = null,
        string? productId = null,
        string? userId = null, 
        string? benefitsCardNo = null, 
        string? districtId = null,
        string? code = null,
        bool? isUsed = null,
        CancellationToken token = default)
    {
        var query = _localSqliteDb.Queryable<ConsumeCode>()
            .Where(x => x.SystemId == systemId);

        if (merchantId is not null)
            query = query.Where(x => x.MerchantId == merchantId);

        if (productId is not null)
            query = query.Where(x => x.ProductId == productId);

        if (userId is not null)
            query = query.Where(x => x.UserId == userId);

        if (benefitsCardNo is not null)
            query = query.Where(x => x.BenefitsCardNo == benefitsCardNo);

        if (districtId is not null)
            query = query.Where(x => x.DistrictId == districtId);

        if (code is not null)
            query = query.Where(x => x.Code == code);

        if (isUsed is not null)
            query = query.Where(x => x.IsUsed == isUsed.Value);

        return await query.ToListAsync(token);
    }

    public async Task AddConsumeCodeAsync(ConsumeCode consumeCode, CancellationToken token = default)
    {
        var id = await _localSqliteDb.Insertable(consumeCode).ExecuteReturnIdentityAsync(token);
        consumeCode.Id = id;
    }

    public async Task AddConsumeCodesAsync(List<ConsumeCode> consumeCodes, CancellationToken token = default)
    {
        await _localSqliteDb.Insertable(consumeCodes).ExecuteCommandAsync(token);
    }

    public async Task UpdateConsumeCodesAsync(ConsumeCode consumeCode, CancellationToken token = default)
    {
        await _localSqliteDb.Updateable(consumeCode).ExecuteCommandAsync(token);
    }

    public async Task<SoftwareSystem?> GetSoftwareSystemAsync(string systemId, CancellationToken token = default)
    {
        return await _localSqliteDb.Queryable<SoftwareSystem>().FirstAsync(x => x.Id == systemId, token);
    }

    public async Task<List<SoftwareSystem>> GetAllSoftwareSystemsAsync(CancellationToken token = default)
    {
        return await _localSqliteDb.Queryable<SoftwareSystem>().ToListAsync(token);
    }

    public async Task AddSoftwareSystemAsync(SoftwareSystem system, CancellationToken token = default)
    {
        await _localSqliteDb.Insertable(system).ExecuteCommandAsync(token);
    }

    public async Task AddSoftwareSystemsAsync(List<SoftwareSystem> systems, CancellationToken token = default)
    {
        await _localSqliteDb.Insertable(systems).ExecuteCommandAsync(token);
    }

    public async Task<CrmebSystemAdmin?> LoginAsCrmebMerchantAsync(string userName, string password, CancellationToken token = default)
    {
        var passwordHash = CryptoUtils.EncryptCrmebPassword(password, userName);
        var query = _crmebMysqlDb.Queryable<CrmebSystemAdmin>()
            .Where(x => x.Account == userName &&
                        x.Password == passwordHash &&
                        x.MerchantId > 0 &&
                        x.IsDeleted == 0
            );

        var systemAdmin = await query.FirstAsync(token);
        return systemAdmin;
    }
}